package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.QuestionDAO;
import com.googlecode.bip.dao.utils.DBHelper;
import com.googlecode.bip.dao.utils.MappableInstance;
import com.googlecode.bip.entity.IQuestion;
import com.googlecode.bip.entity.ITopic;
import com.googlecode.bip.entity.IllegalAttributeException;
import com.googlecode.bip.entity.IllegalIdLocalException;
import com.googlecode.bip.entity.IllegalValueException;
import com.googlecode.bip.entity.impl.Question;
import cx.ath.choisnet.sql.SQLTools;

/**
 * MySQL implementation of {@link QuestionDAO}
 */
public class MySQLQuestionDAO
    extends MySQLDAOBuilder<IQuestion>
        implements QuestionDAO
{
    private static final long serialVersionUID = 1L;
    private static final transient Logger logger = Logger.getLogger( MySQLQuestionDAO.class );

    @Override //ListableInstance<IQuestion>
    public IQuestion newInstance( final ResultSet rSet )
        throws SQLException
    {
        try {
            return loadTranslation( new Question().set( rSet ) );
            }
        catch( NamingException e ) {
            throw new RuntimeException( e );
            }
    }

    @Override
    public IQuestion findQuestion( final int idquestion, final String idlocal )
        throws NamingException, SQLException
    {
       final String sql = "SELECT * FROM `questionsview` WHERE `idquestion`="
           + idquestion
           + " AND `idlocal`='"
           + idlocal
           +"';";

       return getElement( sql );
    }

    @Override
    public List<IQuestion> findQuestions( final int idquestion )
        throws SQLException, NamingException
    {
       final String sql = "SELECT * FROM `questionsview` WHERE `idquestion`="
           + idquestion
           + ";";

       return getList( sql );
    }

    @Override
    public List<IQuestion> findQuestions( final int idtopic, final String idlocal )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `questionsview` WHERE `idtopic`='"
            + idtopic
            + "' AND `idlocal`='"
            + idlocal
            + "';";

        return getList( sql );
    }

    @Override
    public List<IQuestion> findQuestions( final ITopic topic, final String idlocal )
        throws SQLException, NamingException
    {
        return findQuestions( topic.getIdTopic(), idlocal );
    }

    @Override
    public IQuestion findQuestion( final String questionname, final String idlocal )
        throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `questionsview` WHERE `questionname`='"
            + SQLTools.parseFieldValue( questionname )
            + "' AND `idlocal`='"
            + idlocal
            + "';";

        return getElement( sql );
    }

    @Override
    public List<IQuestion> findQuestions( final String questionname, final String idlocal )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `questionsview` WHERE `questionname`='"
            + SQLTools.parseFieldValue( questionname )
            + "' AND `idlocal`='"
            + idlocal
            + "';";

        return getList( sql );
    }

    /**
     * List of idquestion for a specified topic
     *
     * @param idtopic Topic id
     * @return a List of IQuestion
     * @throws SQLException if any
     * @throws NamingException if any
     */
    @Override
    public List<Integer> findQuestionIds( final int idtopic )
       throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `questionsview` WHERE `idtopic`='"
            + idtopic
            + "';";

        if( logger.isDebugEnabled() ) {
            logger.debug( "findQuestionIds: " + sql );
               }

        Connection connection = getConnection();

        try {
            return DBHelper.getListOfInteger( "idquestion", connection, sql );
            }
        finally {
            closeConnection( connection );
            }
    }

//   @Override
//   @Deprecated
//   public boolean insertQuestion( final IQuestion iquestion )
//       throws NamingException, SQLException
//   {
//       IQuestion q = insertQuestion(
//               iquestion.getParentIdTopic(),
//               iquestion.getMinValue(),
//               iquestion.getMaxValue(),
//               iquestion.getDefaultValue(),
//               iquestion.getIdLocal(),
//               iquestion.getName(),
//               iquestion.getDescription()
//               );
//
//       if( q == null ) {
//           return false;
//           }
//       else {
//           Question question = Question.class.cast( iquestion );
//
//           // TO DO: remove method or improve this to be sure
//           // to have a clone.
//           question.setIdQuestion( q.getIdQuestion() );
//           question.setOrder( q.getOrder() );
//
//           if( ! q.equals( question ) ) {
//               throw new RuntimeException( "bug. Missing member during clonning" );
//               }
//
//           return true;
//           }
//   }

    @Override
    public IQuestion insertQuestion(
           final int      idtopic,
           final int      minvalue,
           final int      maxvalue,
           final int      defaultvalue,
           final String   idlocal,
           final String   name,
           final String   description
           )
       throws NamingException, SQLException, IllegalIdLocalException
   {
       if( minvalue > maxvalue ) {
           throw new IllegalArgumentException( "minvalue > maxvalue" );
           }
       if( defaultvalue > maxvalue ) {
           throw new IllegalArgumentException( "defaultvalue > maxvalue" );
           }
       if( defaultvalue < minvalue ) {
           throw new IllegalArgumentException( "defaultvalue < minvalue" );
           }

       final String sql = "CALL `add_question`( '"
           + MySQLLocalDAO.static_getValidIdLocal( idlocal )
           + "', '"
           + SQLTools.parseFieldValue( name )
           + "', '"
           + SQLTools.parseFieldValue( description )
           + "', 0, " // order
           + idtopic // parent idtopic
           + ", "
           + minvalue
           + ", "
           + maxvalue
           + ", "
           + defaultvalue
           + " );";

       if( logger.isDebugEnabled() ) {
           logger.debug( "insertQuestion: " + sql );
           }

       return executeQuery( sql );
    }

    @Override
    public IQuestion insertQuestion(
            final IQuestion questionBase,
            final String    idlocal,
            final String    name,
            final String    description
            ) throws SQLException, NamingException, IllegalIdLocalException
    {
        final String sql = "CALL `update_question`( "
            + questionBase.getIdQuestion()
            + ", '"
            + MySQLLocalDAO.static_getValidIdLocal( idlocal )
            + "', '"
            + SQLTools.parseFieldValue( name )
            + "', '"
            + SQLTools.parseFieldValue( description )
            + "', "
            + questionBase.getOrder()
            + ", "
            + questionBase.getParentIdTopic()
            + ", "
            + questionBase.getIsActive()
            + ", "
            + questionBase.isPrivate()
            + ", "
            + questionBase.getMinValue()
            + ", "
            + questionBase.getMaxValue()
            + ", "
            + questionBase.getDefaultValue()
            + " );";

         return executeQuery( sql );
    }

    @Override
    public boolean updateQuestion( final IQuestion iquestion )
        throws NamingException, SQLException, IllegalIdLocalException
    {
        final String sql = "CALL `update_question`( "
           + iquestion.getIdQuestion()
           + ", '"
           + MySQLLocalDAO.static_getValidIdLocal( iquestion.getIdLocal() )
           + "', '"
           + SQLTools.parseFieldValue( iquestion.getName() )
           + "', '"
           + SQLTools.parseFieldValue( iquestion.getDescription() )
           + "', "
           + iquestion.getOrder()
           + ", "
           + iquestion.getParentIdTopic()
           + ", "
           + iquestion.getIsActive()
           + ", "
           + iquestion.isPrivate()
           + ", "
           + iquestion.getMinValue()
           + ", "
           + iquestion.getMaxValue()
           + ", "
           + iquestion.getDefaultValue()
           + " );";

        doUpdate( sql );

        return true;
    }

//    @Override
//    public int updateQuestionValueDescription(
//            final int       idquestion,
//            final int       value,
//            final String    idlocal,
//            final String    valueDescription
//            )
//        throws  SQLException,
//                NamingException,
//                IllegalArgumentException,
//                IllegalIdLocalException,
//                IllegalValueException
//    {
//        IQuestion iquestion = findQuestion( idquestion, idlocal );
//
//        if( iquestion != null ) {
//            return updateQuestionValueDescription( iquestion, value, valueDescription );
//            }
//    }

    @Override
    public int updateQuestionValueDescription(
            final IQuestion iquestion,
            final int       value,
            final String    valueDescription
            )
        throws  SQLException,
                NamingException,
                IllegalAttributeException
    {
        if( value < iquestion.getMinValue() ) {
            throw new IllegalValueException( value, iquestion.getMinValue(), iquestion.getMaxValue() );
            }
        if( value > iquestion.getMaxValue() ) {
            throw new IllegalValueException( value, iquestion.getMinValue(), iquestion.getMaxValue() );
            }

        String sql = "REPLACE INTO `questionvalues_local` ( `idquestion`, `idlocal`, `value`, `valuedescription` ) VALUE ( '"
            + iquestion.getIdQuestion()
            + "', '"
            + iquestion.getIdLocal()
            + "', '"
            + value
            + "', '"
            + SQLTools.parseFieldValue( valueDescription )
            + "');";

        return doUpdate( sql );
    }

    @Override
    public boolean deleteQuestion( final int idquestion )
        throws NamingException, SQLException
    {
        final String sql = "DELETE FROM `questions` WHERE `idquestion`='"
                + idquestion + "';";

        return doUpdate( sql ) == 1;
    }

    @Override
    public int deleteQuestionLocal( final int idquestion, final String idlocal )
            throws NamingException, SQLException
    {
        return doUpdate(
                "DELETE FROM `questions_local` WHERE `idquestion`='"
                    + idquestion
                    + "' AND `idlocal`='"
                    + idlocal
                    + "';" )
             + doUpdate(
                      "DELETE FROM `questionvalues_local` WHERE `idquestion`='"
                      + idquestion
                      + "' AND `idlocal`='"
                      + idlocal
                      + "';" );
    }

    @Override
    public int deleteQuestionLocals( final int idquestion )
        throws NamingException, SQLException
    {
        return doUpdate(
                "DELETE FROM `questions_local` WHERE `idquestion`='"
                    + idquestion
                    + "';"
                    )
             + doUpdate(
                "DELETE FROM `questionvalues_local` WHERE `idquestion`='"
                    + idquestion
                    + "';"
                    );
    }

    public Question loadTranslation( final Question question )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `questionvalues_local` WHERE `idquestion`='"
                            + question.getIdQuestion()
                            + "' AND `idlocal`='"
                            + question.getIdLocal()
                            + "';";
        final Map<Integer,String> map;

        if( logger.isDebugEnabled() ) {
            logger.debug( "getMap - SQL: " + sql );
            }

        Connection connection = getConnection();

        try {
            map = DBHelper.getMap(
                    new MappableInstance<Integer,String>()
                    {
                        @Override
                        public Integer newKey( ResultSet resultSet ) throws SQLException
                        {
                            return resultSet.getInt( "value" );
                        }
                        @Override
                        public String newValue( ResultSet resultSet ) throws SQLException
                        {
                            return resultSet.getString( "valuedescription" );
                        }
                    },
                    connection,
                    sql
                    );
            }
        finally {
            closeConnection( connection );
            }

        try {
            for( int value = question.getMinValue(); value <= question.getMaxValue(); value++ ) {
                question.set( value, map.get( value ) );
                }
            }
        catch( IllegalValueException e ) {
            throw new RuntimeException( e );
            }

        return question;
    }

}
